if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_hysj_saveLeave')
          and type in ('P','PC'))
   drop procedure bd_proc_hysj_saveLeave
go

CREATE procedure bd_proc_hysj_saveLeave (@startDate date,@endDate date,@description VARCHAR (2000),@totalDuration DECIMAL,@orderNo VARCHAR(10),@leaveId VARCHAR(20),@operator VARCHAR(20),@orderId NUMERIC,@ishaveedu NUMERIC,@applyType NUMERIC,@startTime VARCHAR(10),@endTime VARCHAR(10)) as
BEGIN
	DECLARE @user_serial NUMERIC
	DECLARE @dept_serial numeric --本年福利固定不变
	DECLARE @dept_name VARCHAR(100) --临时数据
	DECLARE @count INT
	DECLARE @totalCount int

	DECLARE @status numeric --单子审批状态
	--假类信息
	DECLARE @breakOff numeric --调休额度
	DECLARE @yearDay numeric --年假额度
	DECLARE @lactationalOff numeric --授乳假额度
	--授乳假有效时间范围
	DECLARE @lactationalStartDate date--授乳假开始有效期
	DECLARE @lactationalEndDate date--授乳假结束有效期
	DECLARE @errorMessage VARCHAR(100) --授乳假错误信息

	DECLARE @ho_year int
	DECLARE @ho_month int

	set @ho_year = YEAR(GETDATE())
	set @ho_month = MONTH(GETDATE())

	DECLARE @firstDay varchar(10) = concat(year(GETDATE()),'-01-01')
	DECLARE @fdClearDay varchar(10)

	select top 1 @fdClearDay = concat(year(GETDATE()),SUBSTRING(convert(varchar(10),fd_nj_clear_date,120), 5, 6)) from wt_reg;

	DECLARE @isOK int=0 --用于判断假期额度是否足够 0足够  1不足

	begin try
		begin tran
			--创建 返回不满足请假的人的原因
			create table #return_table(
				user_serial numeric not null ,
				user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS not NULL
			)
			--创建可提交的人员列表
			select * into #qualified_people from bd_temp_checkPeople where 1=2;
			--定义游标
			--声明一个游标userOfLeave
			DECLARE userOfLeave CURSOR LOCAL FOR SELECT
				b.user_serial,
				u.user_dep as dept_serial,
				b.dept_name
			FROM
				bd_temp_checkPeople b
				INNER JOIN dt_user u on u.user_serial = b.user_serial
			WHERE order_id = @orderId

			OPEN userOfLeave FETCH NEXT FROM userOfLeave INTO @user_serial,@dept_serial,@dept_name--提取第一条数据存放在变量中
			WHILE @@fetch_status = 0
				BEGIN
				--重置@totalCount
					set @totalCount = 0;--处理请假日期中包含在封账日期内，过滤掉，提示：部分请假时间包含在封账时段内，这个和另外（下面的）两种不符合条件独立出来，这里不符合，就不去验证其他。


					select @count = count(1) from bd_kq_accounts a
					INNER JOIN (
					SELECT
						@user_serial as userSerial,*
					FROM
						( SELECT YEAR ( CONVERT ( DATE, leave_date )) AS leaveyear, MONTH ( CONVERT ( DATE, leave_date )) AS leavemonth FROM bd_temp_dayDetail where order_id = @orderId ) b
					GROUP BY
						b.leaveyear,
						b.leavemonth) c on a.user_serial = c.userSerial and a.year = c. leaveyear and a.month = c.leavemonth
				--更新@totalCount
					set @totalCount = @count;
				if (@count>0)
					BEGIN
						--请假的日期中有已封账的日期
						insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'部分请假时间包含在封账时段内'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
					END
					--不存在封账的日期
					ELSE BEGIN
						--1、请假人的相同请假日期是否存在相同的假类（和已请过的假单明细对比）
						select
							@count = count(1)
						from bd_temp_checkPeople p
						INNER JOIN lr_kqingj l on l.user_serial = p.user_serial
						INNER JOIN bd_temp_dayDetail d on d.leave_date = CONVERT(varchar(10),l.kssj,120)
						INNER JOIN bd_leave_order o on o.order_id = l.order_id
						where p.user_serial = @user_serial and p.order_id = @orderId and d.order_id = @orderId
						and l.bh = @leaveId
						and o.status!=2
						and o.is_del!=-1
						and l.whether_validity!=2;
						--更新@totalCount
						set @totalCount = @count;
						--@count>0表示本次请假日期，和已请假有冲突
						if(@count>0)
							BEGIN
								insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'请假类型冲突(同天不能同假)'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
						END

						--2、请假人假类额度是否充足（本次请假的天数*每天请假的时长要小于可用额度）
						--判断该假有额度功能,这里先设固定值，之后修改为查询获得
						--select @ishaveedu = isnull(ishaveedu,0) from kt_qingj where bh = @leaveId;
						--
						if @ishaveedu!=0
						BEGIN
							if @ishaveedu=1 --调休
							BEGIN
								select @breakOff = rest_total
								from bd_kq_holidays_time where user_serial = @user_serial;
								--调休是否为null
								if @breakOff is null set @breakOff=0
								if(@totalDuration>@breakOff)
									BEGIN
										set @isOK = 1;
									END

							END
							ELSE IF @ishaveedu=2 --年假
							BEGIN
								select @yearDay = vacation_annual
								from bd_kq_holidays_time where user_serial = @user_serial;
								--年假是否为null
								if @yearDay is null set @yearDay=0
								if(@totalDuration>@breakOff)
									BEGIN
										set @isOK = 1;
									END
							END
							ELSE IF @ishaveedu=3 --授乳假
							BEGIN
								--查看授乳假是否是在授乳假有效时间范围内申请，超过有效期不能再申请
								select
									@lactationalStartDate = lactational_begin,
									@lactationalEndDate = lactational_end
								from dt_user where user_serial = @user_serial;
								--判断授乳假有效开始时间 授乳假有效结束时间
								if (@lactationalStartDate is null and @lactationalEndDate is null)
								BEGIN
									set @isOK = 2
									set @errorMessage = '授乳假有效开始,结束时间都为空';
								END

								if (@lactationalStartDate is null and @errorMessage is null)
								BEGIN
									set @isOK = 2
									set @errorMessage = '授乳假有效开始时间为空';
								END

								if (@lactationalStartDate is null and @errorMessage is null)
								BEGIN
									set @isOK = 2
									set @errorMessage = '授乳假有效结束时间为空';
								END

								if @isOK != 2
								BEGIN
									if((@lactationalStartDate<@startDate) or (@lactationalEndDate<@endDate))
									BEGIN
										set @isOK = 2
										set @errorMessage = '请在授乳假有效时间范围内申请';
									END
								END

								select @lactationalOff = vacation_lactational
								from bd_kq_holidays_time where user_serial = @user_serial;
								--授乳假是否为null
								if @lactationalOff is null set @lactationalOff=0
								if(@totalDuration>@breakOff)
									BEGIN
										set @isOK = 1;
									END
							END

							if @isOK = 1
								BEGIN
									--积累
									set @totalCount = @totalCount+1;
									--判断临时表#return_table中是否有这个人的信息，有则修改，无则添加
									select @count = count(1) from #return_table where user_serial = @user_serial;
									if(@count>0)
										BEGIN
											update #return_table set error_msg = '1.'+error_msg+'，2.假类可用额度不足' where user_serial = @user_serial;
										END
										ELSE BEGIN
											insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
											SELECT
												u.user_serial,
												u.user_no,
												u.user_lname,
												d.dep_name,
												'假类可用额度不足'
											FROM
												dt_user u
												INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
												where u.user_serial = @user_serial
										END
								END

							--查看是否在授乳假有效时间范围内申请
							if @isOK = 2
								BEGIN
									--积累
									set @totalCount = @totalCount+1;
									--判断临时表#return_table中是否有这个人的信息，有则修改，无则添加
									select @count = count(1) from #return_table where user_serial = @user_serial;
									if(@count>0)
										BEGIN
											update #return_table set error_msg = '1.'+error_msg+'，2.'+@errorMessage where user_serial = @user_serial;
										END
										ELSE BEGIN
											insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
											SELECT
												u.user_serial,
												u.user_no,
												u.user_lname,
												d.dep_name,
												@errorMessage
											FROM
												dt_user u
												INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
												where u.user_serial = @user_serial
										END
								END

						END

					END

					--合格的人则放入#qualified_people
					if @totalCount = 0
						BEGIN
							insert into #qualified_people
							select  * from bd_temp_checkPeople where user_serial = @user_serial and order_id = @orderId;
						END
					FETCH NEXT FROM userOfLeave INTO @user_serial,@dept_serial,@dept_name
				END

			--根据#qualified_people中是否有数据，来判断是否有人不满足请假条件，有满足，则保存请假单和请假明细
			select @count = count(1) from #qualified_people;
			if(@count>0)
				BEGIN
					set @status = 0;
					--保存请假单
					insert into bd_leave_order(
						order_id,
						order_no,
						leave_id,
						start_date,
						end_date,
						creater_id,
						people_num,
						total_time,
						remark,
						apply_type,
						status,
						begin_time,
						end_time
					)VALUES(
						@orderId,
						@orderNo,
						@leaveId,
						@startDate,
						@endDate,
						@operator,
						@count,
						@totalDuration*@count,
						@description,
						@applyType,
						@status,
						@startTime,
						@endTime
					)
					--保存请假明细
						insert into lr_kqingj(user_serial,bh,kssj,jssj,jsjg,gly_no,order_id)
						SELECT
							p.user_serial,
							d.leave_id,
							d.leave_date,
							d.leave_date,
							d.leave_minute,
							@operator,
							d.order_id
						FROM
							bd_temp_dayDetail d,
							#qualified_people p
						WHERE
							d.order_id = @orderId

					--遍历调用
					DECLARE userOfReset CURSOR LOCAL FOR SELECT user_serial FROM #qualified_people

					OPEN userOfReset FETCH NEXT FROM userOfReset INTO @user_serial
					WHILE @@fetch_status = 0
						BEGIN
								--重新计算
								exec bd_proc_reset_data @startDate,@ishaveedu,@user_serial,@operator

							FETCH NEXT FROM userOfReset INTO @user_serial
						END
				END

			--返回的数据
			SELECT
						user_no AS 'userNo',
						user_name AS 'userName',
						dept_name AS 'deptName',
						error_msg as 'errorMsg'
						from #return_table;

			 --计算考勤
      DECLARE @users VARCHAR(MAX) = ''

      SELECT
        @users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
      FROM #qualified_people u
      WHERE u.order_id = @orderId

			SET @users = SUBSTRING(@users,0, LEN(@users))
      EXEC bd_proc_timecard_extend @startDate, @endDate, @users,2

			delete from bd_temp_dayDetail where order_id = @orderId;
			delete from bd_temp_checkPeople where order_id = @orderId;

			DROP TABLE #return_table;
			DROP TABLE #qualified_people;
			commit tran
		end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
				CLOSE userOfLeave --关闭游标
				DEALLOCATE userOfLeave --释放游标
			END;
	end catch
END
GO